# Google Cloud SQL for PostgreSQL

[Cloud SQL](https://cloud.google.com/sql) is a fully managed relational database service that offers high
performance, seamless integration, and impressive scalability and offers database engines such as PostgreSQL.

This guide provides a quick overview of how to use Cloud SQL for PostgreSQL to load Documents with the `PostgresLoader` class.

## Overview

### Before you begin

In order to use this package, you first need to go through the following steps:

1.  [Select or create a Cloud Platform project.](https://developers.google.com/workspace/guides/create-project)
2.  [Enable billing for your project.](https://cloud.google.com/billing/docs/how-to/modify-project#enable_billing_for_a_project)
3.  [Enable the Cloud SQL Admin API.](https://console.cloud.google.com/flows/enableapi?apiid=sqladmin.googleapis.com)
4.  [Setup Authentication.](https://cloud.google.com/docs/authentication)
5.  [Create a CloudSQL instance](https://cloud.google.com/sql/docs/postgres/connect-instance-auth-proxy#create-instance)
6.  [Create a CloudSQL database](https://cloud.google.com/sql/docs/postgres/create-manage-databases)
7.  [Add a user to the database](https://cloud.google.com/sql/docs/postgres/create-manage-users)

### Authentication

Authenticate locally to your Google Cloud account using the `gcloud auth login` command.

### Set Your Google Cloud Project

Set your Google Cloud project ID to leverage Google Cloud resources locally:

```bash
gcloud config set project YOUR-PROJECT-ID
```

If you don't know your project ID, try the following:

- Run `gcloud config list`.
- Run `gcloud projects list`.
- See the support page: [Locate the project ID](https://support.google.com/googleapi/answer/7014113).

## Setting up a PostgresLoader instance

To use the PostgresLoader class, you'll need to install the `@langchain/google-cloud-sql-pg`
package and then follow the steps bellow.

First, you'll need to log in to your Google Cloud account and set the following environment variables based
on your Google Cloud project; these will be defined based on how you want to configure (fromInstance,
fromEngine, fromEngineArgs) your PostgresEngine instance:

```bash
PROJECT_ID="your-project-id"
REGION="your-project-region" // example: "us-central1"
INSTANCE_NAME="your-instance"
DB_NAME="your-database-name"
DB_USER="your-database-user"
PASSWORD="your-database-password"
```

### Setting up an instance

To instantiate a PostgresLoader, you'll first need to create a database connection through the
PostgresEngine.

```typescript
import {
  PostgresLoader,
  PostgresEngine,
  PostgresEngineArgs,
} from "@langchain/google-cloud-sql-pg";
import * as dotenv from "dotenv";

dotenv.config();

const peArgs: PostgresEngineArgs = {
  user: process.env.DB_USER ?? "",
  password: process.env.PASSWORD ?? "",
};

// PostgresEngine instantiation
const engine: PostgresEngine = await PostgresEngine.fromInstance(
  process.env.PROJECT_ID ?? "",
  process.env.REGION ?? "",
  process.env.INSTANCE_NAME ?? "",
  process.env.DB_NAME ?? "",
  peArgs
);
```

### Load Documents using the table_name argument

The loader returns a list of Documents from the table using the first column as page_content and all other columns as metadata. The default table will have the first column as page_content and the second column as metadata (JSON). Each row becomes a document.

```js
const documentLoaderArgs: PostgresLoaderOptions = {
  tableName: "test_table_custom",
  contentColumns: ["fruit_name", "variety"],
  metadataColumns: [
    "fruit_id",
    "quantity_in_stock",
    "price_per_unit",
    "organic",
  ],
  format: "text",
};

const documentLoaderInstance = await PostgresLoader.initialize(
  PEInstance,
  documentLoaderArgs
);
```

### Load Documents using a SQL query

The query parameter allows users to specify a custom SQL query which can include filters to load specific documents from a database.

```js
const documentLoaderArgs: PostgresLoaderOptions = {
  query: "SELECT * FROM my_fruit_table",
  contentColumns: ["fruit_name", "variety"],
  metadataColumns: [
    "fruit_id",
    "quantity_in_stock",
    "price_per_unit",
    "organic",
  ],
  format: "text",
};

const documentLoaderInstance = await PostgresLoader.initialize(
  PEInstance,
  docucumetLoaderArgs
);
```

### Set page content format

The loader returns a list of Documents, with one document per row, with page content in specified string format, i.e. text (space separated concatenation), JSON, YAML, CSV, etc. JSON and YAML formats include headers, while text and CSV do not include field headers.
